package com.etc.utils;

import com.etc.entity.sensor.BizSensorConfig;
import com.etc.entity.warning.BizThreshold;
import com.etc.result.CodeMessage;
import com.etc.result.ResultBean;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import lombok.extern.slf4j.Slf4j;

import java.io.File;
import java.io.IOException;
import java.util.List;

/**
 * @Author : DDS
 * @Description : jxlExcel工具类
 * @Date: 2019-12-03 19:17
 */
@Slf4j
public class JxlExcelUtil {

    /**
     * Excel的导出
     *
     * @param bizSensorConfigs 从数据库查询到的List<BizSensorConfig>
     * @return 统一的返回ResultBean
     */
    public static ResultBean downExcel(List<BizSensorConfig> bizSensorConfigs) {

        WritableWorkbook book = null;
        try {
            // 打开文件
            book = Workbook.createWorkbook(getxlsFile());
            // 生成名为"学生"的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("传感器", 0);
            //生成第一行的表头
            sheet.addCell(new Label(0, 0, "指标类型 "));
            sheet.addCell(new Label(1, 0, "传感器位置 "));
            sheet.addCell(new Label(2, 0, "桥跨 "));
            sheet.addCell(new Label(3, 0, "排序 "));
            sheet.addCell(new Label(4, 0, "传感器编号 "));
            sheet.addCell(new Label(5, 0, "类型 "));
            sheet.addCell(new Label(6, 0, "中心波长(nm)"));
            sheet.addCell(new Label(7, 0, "保护带宽(nm)"));
            sheet.addCell(new Label(8, 0, "传感器系数 "));
            sheet.addCell(new Label(9, 0, "补偿传感器编号 "));
            sheet.addCell(new Label(10, 0, "补偿系数 "));
            sheet.addCell(new Label(11, 0, "公式"));

            //判断数据是否为空
            if (StringUtil.isNotNull(bizSensorConfigs)) {

                //遍历List中的数据
                for (int i = 1; i < bizSensorConfigs.size() + 1; i++) {
                    sheet.addCell(new Label(0, i, bizSensorConfigs.get(i - 1).getIndexType()));
                    sheet.addCell(new Label(1, i, bizSensorConfigs.get(i - 1).getSensorPosition()));
                    sheet.addCell(new Label(2, i, bizSensorConfigs.get(i - 1).getBridgeSpan()));
                    sheet.addCell(new Label(3, i, bizSensorConfigs.get(i - 1).getSort() != null? String.valueOf(bizSensorConfigs.get(i - 1).getSort()):""));
                    sheet.addCell(new Label(4, i, bizSensorConfigs.get(i - 1).getSensorNumber()));
                    sheet.addCell(new Label(5, i, bizSensorConfigs.get(i - 1).getType()));
                    sheet.addCell(new Label(6, i, bizSensorConfigs.get(i - 1).getCentreWavelength() != null? String.valueOf(bizSensorConfigs.get(i - 1).getCentreWavelength()):""));
                    sheet.addCell(new Label(7, i, bizSensorConfigs.get(i - 1).getBandwidth() != null? String.valueOf(bizSensorConfigs.get(i - 1).getBandwidth()):""));
                    sheet.addCell(new Label(8, i, bizSensorConfigs.get(i - 1).getSensorCoefficient() != null?String.valueOf(bizSensorConfigs.get(i - 1).getSensorCoefficient()):""));
                    sheet.addCell(new Label(9, i, bizSensorConfigs.get(i - 1).getOffsetSensorNumber()));
                    sheet.addCell(new Label(10, i, bizSensorConfigs.get(i - 1).getOffsetCoefficient() != null ?String.valueOf(bizSensorConfigs.get(i - 1).getOffsetCoefficient()):""));
                    sheet.addCell(new Label(11, i, bizSensorConfigs.get(i - 1).getFormula()));
                }
            }
            // 写入数据并关闭文件
            book.write();
            return ResultBean.success();
        } catch (Exception e) {
            log.error(e.getMessage());
            return ResultBean.error(CodeMessage.DOWN_DEFEATED.getCode(), CodeMessage.DOWN_DEFEATED.getMassage());
        } finally {
            if (book != null) {
                try {
                    book.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static File getxlsFile() throws IOException {
        File reslt = new File("D:\\price.xls");
        reslt.deleteOnExit();
        reslt.createNewFile();
        return reslt;
    }

    /**
     * Excel的导出
     *
     * @param bizThresholds 从数据库查询到的List<BizThreshold>
     * @return 统一的返回ResultBean
     */
    public static ResultBean downExcel_threshold(List<BizThreshold> bizThresholds) {

        WritableWorkbook book = null;
        try {
            // 打开文件
            book = Workbook.createWorkbook(getxlsFile_threshold());
            // 生成名为"学生"的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("预警阈值", 0);
            //生成第一行的表头
            sheet.addCell(new Label(0, 0, "编号 "));
            sheet.addCell(new Label(1, 0, "创建者 "));
            sheet.addCell(new Label(2, 0, "更新者 "));
            sheet.addCell(new Label(3, 0, "备注信息 "));
            sheet.addCell(new Label(4, 0, "删除标记 "));
            sheet.addCell(new Label(5, 0, "等级 "));
            sheet.addCell(new Label(6, 0, "数值"));
            sheet.addCell(new Label(7, 0, "类型"));
            sheet.addCell(new Label(8, 0, "升级系数 "));
            sheet.addCell(new Label(9, 0, "设备位置 "));
            sheet.addCell(new Label(10, 0, "范围标志 "));

            //判断数据是否为空
            if (StringUtil.isNotNull(bizThresholds)) {

                //遍历List中的数据
                for (int i = 1; i < bizThresholds.size() + 1; i++) {
                    sheet.addCell(new Label(0, i, bizThresholds.get(i - 1).getId()));
                    sheet.addCell(new Label(1, i, bizThresholds.get(i - 1).getCreateBy()));
                    sheet.addCell(new Label(2, i, bizThresholds.get(i - 1).getUpdateBy()));
                    sheet.addCell(new Label(3, i, bizThresholds.get(i - 1).getRemarks()));
                    sheet.addCell(new Label(4, i, bizThresholds.get(i - 1).getDelFlag()));
                    sheet.addCell(new Label(5, i, String.valueOf(bizThresholds.get(i - 1).getLevel())));
                    sheet.addCell(new Label(6, i,  bizThresholds.get(i - 1).getValue()));
                    sheet.addCell(new Label(7, i, String.valueOf(bizThresholds.get(i - 1).getType())));
                    sheet.addCell(new Label(8, i, String.valueOf(bizThresholds.get(i - 1).getRatio())));
                    sheet.addCell(new Label(9, i, bizThresholds.get(i - 1).getSensorPosition()));
                    sheet.addCell(new Label(10, i, String.valueOf(bizThresholds.get(i - 1).getScopeFlag())));
                }
            }
            // 写入数据并关闭文件
            book.write();
            return ResultBean.success();
        } catch (Exception e) {
            log.error(e.getMessage());
            return ResultBean.error(CodeMessage.DOWN_DEFEATED.getCode(), CodeMessage.DOWN_DEFEATED.getMassage());
        } finally {
            if (book != null) {
                try {
                    book.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static File getxlsFile_threshold() throws IOException {
        File reslt = new File("E:\\waring_six.xls");
        reslt.deleteOnExit();
        reslt.createNewFile();
        return reslt;
    }

}